"""
    熊猫分期用户还款行为数据分析

    k-近邻算法分类器
"""
from numpy import *
import operator
import matplotlib.pyplot as plt
import pandas as pd
import pymysql


def createDataSet():
    group = array([[1.0, 1.1], [1.0, 1.0], [0, 0], [0, 0.1]])
    labels = ['A', 'A', 'B', 'B']
    return group, labels


def classify0(inX, dataSet, labels, k):
    """
    k-近邻算法
    使用欧式距离公式计算两个向量点之间的距离
    :param inX:
    :param dataSet:
    :param labels:
    :param k:
    :return:
    """
    dataSetSize = dataSet.shape[0]
    diffMat = tile(inX, (dataSetSize, 1)) - dataSet
    sqDiffMat = diffMat ** 2
    sqDistances = sqDiffMat.sum(axis=1)
    distances = sqDistances ** 0.5
    sortedDistIndicies = distances.argsort()
    classCount = {}
    for i in range(k):
        voteIlabel = labels[sortedDistIndicies[i]]
        classCount[voteIlabel] = classCount.get(voteIlabel, 0) + 1
    sortedClassCount = sorted(classCount.items(), key=operator.itemgetter(1), reverse=True)
    return sortedClassCount[0][0]


def file2matrix(filename):
    """
    从文本文件中解析数据
    :param filename:
    :return:
    """
    fr = open(filename)
    arrayOLines = fr.readlines()
    numberOfLines = len(arrayOLines)
    # returnMat = zeros((numberOfLines, 3))
    returnMat = zeros((numberOfLines, 6))
    classLabelVector = []
    index = 0
    for line in arrayOLines:
        line = line.strip()
        listFormLine = line.split(',')
        # returnMat[index, :] = listFormLine[0:3]
        returnMat[index, :] = listFormLine[0:6]
        classLabelVector.append(int(listFormLine[-1]))
        index += 1
    return returnMat, classLabelVector


def autoNorm(dataSet):
    """
    归一化特征值
    :param dataSet:
    :return:
    """
    minVals = dataSet.min(0)
    maxVals = dataSet.max(0)
    ranges = maxVals - minVals
    normDataSet = zeros(shape(dataSet))
    m = dataSet.shape[0]
    normDataSet = dataSet - tile(minVals, (m, 1))
    normDataSet = normDataSet / tile(ranges, (m, 1))
    return normDataSet, ranges, minVals


def datingClassTest():
    """
    分类器测试
    :return:
    """
    hoRatio = 0.10
    datingDataMat, datingLabels = file2matrix('datingTestSet2.txt')
    normMat, ranges, minVals = autoNorm(datingDataMat)
    m = normMat.shape[0]
    numTestVecs = int(m * hoRatio)
    errorCount = 0.0
    for i in range(numTestVecs):
        classifierResult = classify0(normMat[i, :], normMat[numTestVecs:m, :], datingLabels[numTestVecs:m], 3)
        print("the classifier came back with: %d, the real answer is: %d" % (classifierResult, datingLabels[i]))
        if (classifierResult != datingLabels[i]):
            errorCount += 1.0
    print("thie total error rate is: %f" % (errorCount / float(numTestVecs)))


def classifyPerson():
    """
    预测数据
    :return:
    """
    resultList = ['未还款(未逾期)', '已还款', '逾期未还']
    percentTats = float(input("percenttage of time spent playing video games?"))
    ffMiles = float(input("frequent flier miles earned per year?"))
    iceCream = float(input("liters of ice cream consumed per year?"))
    datingDataMat, datingLabels = file2matrix('datingTestSet2.txt')
    normMat, ranges, minVals = autoNorm(datingDataMat)
    inArr = array([ffMiles, percentTats, iceCream])
    classifierResult = classify0((inArr - minVals) / ranges, normMat, datingLabels, 3)
    print("You will probably like this person: ", resultList[classifierResult - 1])


def classifyPerson2():
    """
    预测数据
    :return:
    """
    resultList = ['未还款(未逾期)', '已还款', '逾期未还']
    age = float(input("年龄?"))
    sex = float(input("性别?"))
    area = float(input("地区?"))
    amount = float(input("借款金额?"))
    period = float(input("借款周期?"))
    periodType = float(input("周期类型?"))
    datingDataMat, datingLabels = file2matrix('xiongmao.txt')
    normMat, ranges, minVals = autoNorm(datingDataMat)
    inArr = array([age, sex, area, amount, period, periodType])
    classifierResult = classify0((inArr - minVals) / ranges, normMat, datingLabels, 3)
    print("还款预测: ", resultList[classifierResult - 1])


def matplotlib_show():
    """
    使用Matplotlib创建散点图
    :return:
    """
    datingDataMat, datingLabels = file2matrix('xiongmao.txt')
    fig = plt.figure()
    ax1 = fig.add_subplot(2, 2, 1)
    ax2 = fig.add_subplot(2, 2, 2)
    ax3 = fig.add_subplot(2, 2, 3)

    ax1.scatter(datingDataMat[:, 0], datingDataMat[:, 1],
                15.0 * array(datingLabels), 15.0 * array(datingLabels))
    ax2.scatter(datingDataMat[:, 2], datingDataMat[:, 3],
                15.0 * array(datingLabels), 15.0 * array(datingLabels))
    ax3.scatter(datingDataMat[:, 4], datingDataMat[:, 5],
                15.0 * array(datingLabels), 15.0 * array(datingLabels))

    plt.show()


def pandas_show():
    """
    利用pandas生成条形图
    :return:
    """
    # df = pd.DataFrame(random.rand(10, 4), columns=['a', 'b', 'c', 'd'])
    datingDataMat, datingLabels = file2matrix('xiongmao.txt')
    df = pd.DataFrame(datingDataMat, columns=['年龄', '性别', '地区', '金额', '周期', '周期类型'])
    df.plot.bar()

    # 解决上面 columns=['年龄', '性别', '地区', '金额', '周期', '周期类型'] 中文显示问题
    plt.rcParams['font.sans-serif'] = ['KaiTi']
    plt.rcParams['font.serif'] = ['KaiTi']

    plt.show()


def get_data_from_sql():
    """
    从数据库获取数据
    :return:
    """
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "root", "demo")

    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # 查询
    sql = "SELECT * FROM user"

    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        print(type(results))
        print(results)
        print(array(results))
        savetxt('user.txt', results, delimiter=',', fmt='%s', encoding='utf8')
        # print(type(array(results)))
        # for row in results:
        #     fname = row[0]
        #     lname = row[1]
        #     age = row[2]
        #     sex = row[3]
        #     income = row[4]
        #     # 打印结果
        #     print("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
        #           (fname, lname, age, sex, income))
    except:
        print("Error: unable to fetch data")

    # 关闭数据库连接
    db.close()


def main():
    # group, labels = createDataSet()
    # print(group)
    # print(labels)
    #
    # print(classify0([0, 0], group, labels, 3))

    # datingDataMat, datingLabels = file2matrix('datingTestSet2.txt')
    # print(datingDataMat)
    # print(datingLabels)

    # normMat, ranges, minVals = autoNorm(datingDataMat)
    # print("-------------------------normMat-------------------------")
    # print(normMat)
    # print("-------------------------ranges--------------------------")
    # print(ranges)
    # print("-------------------------minVals-------------------------")
    # print(minVals)

    # datingClassTest()

    # classifyPerson()

    # classifyPerson2()

    # datingDataMat, datingLabels = file2matrix('xiongmao.txt')
    # print(datingDataMat)
    # print(datingLabels)

    # normMat, ranges, minVals = autoNorm(datingDataMat)
    # print("-------------------------normMat-------------------------")
    # print(normMat)
    # print("-------------------------ranges--------------------------")
    # print(ranges)
    # print("-------------------------minVals-------------------------")
    # print(minVals)

    # matplotlib_show()

    # pandas_show()

    get_data_from_sql()


if __name__ == '__main__':
    main()
